﻿#region namespace
using System;
using System.ComponentModel;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using Touch2Like.SharedLibrary;
#endregion

namespace Touch2Like.DAL
{
    public class Database : IDisposable
    {
        private SqlConnection con;
        private SqlConnection conR;
        private string _connString;
        private string _connStringR;
        private string _replicationFlag;
        SqlTransaction trans;// = new SqlTransaction();

        private static int msThreshold = -1;
        private DateTime startStamp;
        private void startTimer()
        {
            startStamp = System.DateTime.Now;
        }

        private void endTimer(string function, SqlParameter[] parameters)
        {// this one is for procs...
            DateTime endStamp = System.DateTime.Now;
            if (msThreshold == -1)
            {
                if (ConfigurationManager.AppSettings["DBLogThresholdMS"] != null)
                    msThreshold = System.Convert.ToInt32(ConfigurationManager.AppSettings["DBLogThresholdMS"]);
                else
                    msThreshold = 250; // The default.
            }
            int ms = (endStamp - startStamp).Milliseconds;
            if (ms >= msThreshold)
            {
                string sParameters = "";
                if (parameters != null)
                {
                    foreach (SqlParameter p in parameters)
                    {
                        string value = "null";
                        if (p.Value != null)
                            value = "'" + SQL.SQLEncode(p.Value.ToString()) + "'";
                        sParameters = sParameters + p.ParameterName + "=" + value + ",";
                    }
                    if (sParameters.Length > 1)
                        sParameters = Strings.LeftString(sParameters, sParameters.Length - 1);
                }

                Utility.AddDBMessageToTextFile("Indigo", "Query threshold exceeded: " + ms.ToString() + "ms", function, function + " " + sParameters);
            }
        }

        private void endTimer(string data)
        {// this one is for straight up SQL queries
            DateTime endStamp = System.DateTime.Now;
            if (msThreshold == -1)
            {
                if (ConfigurationManager.AppSettings["DBLogThresholdMS"] != null)
                    msThreshold = System.Convert.ToInt32(ConfigurationManager.AppSettings["DBLogThresholdMS"]);
                else
                    msThreshold = 250; // The default.
            }
            int ms = (endStamp - startStamp).Milliseconds;
            if (ms >= msThreshold)
            {
                Utility.AddDBMessageToTextFile("CAMINTEL", "Query threshold exceeded: " + ms.ToString() + "ms", "SQL", data);
            }
        }

        //---------------------------------------------//
        #region constructor
        /// <summary>
        /// Get connection string from web config file
        /// </summary>
        public Database()
        {
            if (ConfigurationManager.AppSettings["DBSettingEncrypt"].ToString().ToLower() == "true")
            {
                _connString = Utility.Encryption.Decrypt(ConfigurationManager.AppSettings["DBName"]);
            }
            else
            {
                _connString = ConfigurationManager.AppSettings["DBName"];
            }
        }
        public Database(string connString)
        {
            if (ConfigurationManager.AppSettings["DBSettingEncrypt"].ToString().ToLower() == "true")
                _connString = Utility.Encryption.Decrypt(connString);
            else
                _connString = connString;
        }

        #endregion
        //---------------------------------------------//
        #region open
        /// <summary>
        /// Open the database connection
        /// </summary>
        public void Open()
        {
            if (con == null)
            {
                con = new SqlConnection(_connString);

                try
                {
                    con.Open();
                }
                catch (Exception ex)
                {
                    string e = ex.Message.ToString();
                }
            }
        }

        public void OpenForExport()
        {
            if (con == null || con.State == ConnectionState.Closed)
            {
                con = new SqlConnection(_connString);

                try
                {
                    con.Open();
                }
                catch (Exception ex)
                {
                    string e = ex.Message.ToString();
                }
            }
        }
        #endregion
        //---------------------------------------------//

        //---------------------------------------------//
        #region openR
        /// <summary>
        /// Open the database connection
        /// </summary>
        public void OpenR()
        {
            if (conR == null)
            {
                conR = new SqlConnection(_connStringR);

                try
                {
                    conR.Open();
                }
                catch (Exception ex)
                {
                    string e = ex.Message.ToString();
                }
            }
        }
        #endregion
        //---------------------------------------------//
        #region make parameter
        /// <summary>
        /// Make parameter of the stored procedure
        /// </summary>
        /// <param name="ParamName">Parameter's name</param>
        /// <param name="DbType">Parameter's type</param>
        /// <param name="Size">Parameter's size</param>
        /// <param name="Direction">Parameter's direction</param>
        /// <param name="Value">Parameter's value</param>
        /// <returns>Sql Parameter</returns>
        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;

            if (Size > 0)
            {
                param = new SqlParameter(ParamName, DbType, Size);
            }
            else
            {
                param = new SqlParameter(ParamName, DbType);
            }

            param.Direction = Direction;

            if (!(Direction == ParameterDirection.Output && Value == null))
            {
                param.Value = Value;
            }
            else
            {
                param.Value = DBNull.Value;
            }

            return param;
        }
        //---------------------------------------------//
        /// <summary>
        /// Make in parameter
        /// </summary>
        /// <param name="ParamName">Parameter's name</param>
        /// <param name="DbType">Parameter's type</param>
        /// <param name="Size">Parameter's size</param>
        /// <param name="Value">Parameter's value</param>
        /// <returns>Sql Parameter</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }
        //---------------------------------------------//
        /// <summary>
        /// Make out parameter
        /// </summary>
        /// <param name="ParamName">Parameter's name</param>
        /// <param name="DbType">Parameter's type</param>
        /// <param name="Size">Parameter's size</param>
        /// <returns>Sql Parameter</returns>
        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }
        #endregion
        //---------------------------------------------//
        #region get dataset
        /// <summary>
        /// Get dataset by stored procedure
        /// </summary>
        /// <param name="procName">Stored procedure's name</param>
        /// <param name="prams">Stored procedure's parameters</param>
        /// <returns>The dataset that conforms to the condition</returns>
        public DataSet GetDataSet(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.CommandTimeout = 0;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();

            startTimer();
            adapter.Fill(dataset);
            endTimer(procName, prams);

            Close();

            cmd = null;

            return dataset;
        }


        /// <summary>
        /// Get dataset by stored procedure
        /// </summary>
        /// <param name="procName">Stored procedure's name</param>
        /// <param name="prams">Stored procedure's parameters</param>
        /// <returns>The dataset that conforms to the condition</returns>
        public DataSet GetDataSet(string procName, SqlParameter[] prams, System.Data.Common.DataTableMappingCollection dtmapC)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.CommandTimeout = 0;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();
            for (int i = 0; i < dtmapC.Count; i++)
                adapter.TableMappings.Add(dtmapC[i].SourceTable, dtmapC[i].DataSetTable);
            startTimer();
            adapter.Fill(dataset);
            endTimer(procName, prams);

            Close();

            cmd = null;

            return dataset;
        }

        //---------------------------------------------//
        /// <summary>
        /// Get dataset by stored procedure
        /// </summary>
        /// <param name="procName">Stored procedure's name</param>
        /// <returns>The dataset that conforms to the condition</returns>
        public DataSet GetDataSet(string procName)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            cmd.CommandTimeout = 0;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();

            startTimer();
            adapter.Fill(dataset);
            endTimer(procName, null);

            Close();

            cmd = null;

            return dataset;
        }

        //---------------------------------------------//
        /// <summary>
        /// Get dataset by sql string
        /// </summary>
        /// <param name="sqlStr">sql stringe</param>
        /// <returns>The dataset that conforms to the condition</returns>
        public DataSet GetDataSetBySQLStr(string sqlStr)
        {
            Open();

            SqlCommand cmd = new SqlCommand(sqlStr, con);
            cmd.CommandTimeout = 0;
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();

            startTimer();
            adapter.Fill(dataset);
            endTimer(sqlStr);

            Close();

            cmd = null;

            return dataset;
        }
        #endregion
        //---------------------------------------------//
        #region execute string
        /// <summary>
        /// Get dataset by the query string
        /// </summary>
        /// <param name="querystr">Query string</param>
        /// <returns>The dataset that conforms to the condition</returns>
        public DataSet ExecuteString(string querystr)
        {
            Open();

            SqlCommand cmd = new SqlCommand(querystr, con);
            cmd.CommandTimeout = 0;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();

            adapter.Fill(dataset);

            Close();

            cmd = null;

            return dataset;
        }
        //---------------------------------------------//
        //added by jove 05/17/06
        /// <summary>
        /// Get dataset by the query string and its parameters
        /// </summary>
        /// <param name="querystr">Querty string</param>
        /// <param name="prams">Parameters</param>
        /// <returns>The dataset that conforms to the condition</returns>
        public DataSet ExecuteString(string querystr, SqlParameter[] prams)
        {
            Open();

            SqlCommand cmd = new SqlCommand(querystr, con);
            cmd.CommandTimeout = 0;
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);
                }
            }

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();

            adapter.Fill(dataset);

            Close();

            cmd = null;

            return dataset;
        }
        //---------------------------------------------//
        //added by jove 05/25/2006
        /// <summary>
        /// Get the first field's value
        /// </summary>
        /// <param name="querystr">Query string</param>
        /// <returns>First field's value</returns>
        public string ExecureString(string querystr)
        {
            Open();

            SqlCommand cmd = new SqlCommand(querystr, con);
            cmd.CommandTimeout = 0;
            return cmd.ExecuteScalar().ToString();
        }
        //---------------------------------------------//
        //added by jove 11/30/2006
        /// <summary>
        /// Get the first field's value
        /// </summary>
        /// <param name="procName">Stored procedure's name</param>
        /// <param name="prams">Parameters</param>
        /// <returns>First field's value</returns>
        public string ExecureString(string procName, SqlParameter[] prams)
        {
            //Open();
            string str = "";

            using (SqlCommand cmd = CreateCommand(procName, prams))
            {
                cmd.CommandTimeout = 0;
                if (con.State == ConnectionState.Closed)
                    con.Open();
                str = cmd.ExecuteScalar().ToString();
                cmd.Parameters.Clear();
                this.Close();

                //cmd = null;
                cmd.Dispose();
            }
            if (_replicationFlag == "True")
            {
                try
                {
                    using (SqlCommand cmdR = CreateCommandR(procName, prams))
                    {
                        cmdR.CommandTimeout = 0;
                        if (conR.State == ConnectionState.Closed)
                            conR.Open();

                        cmdR.ExecuteNonQuery();
                        cmdR.Parameters.Clear();
                        this.Close();

                        cmdR.Dispose();
                    }
                }
                catch (Exception ex)
                {
                    //string e = ex.Message.ToString();
                }
            }
            return str;





        }
        #endregion
        //---------------------------------------------//
        #region execute query
        /// <summary>
        /// Execute a query string and return a SqlDataReader object
        /// </summary>
        /// <param name="querystr">Query string</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ExecuteQuery(string querystr)
        {
            Open();

            SqlCommand cmd = new SqlCommand(querystr, con);
            cmd.CommandTimeout = 0;
            startTimer();
            try
            {
                return cmd.ExecuteReader();
            }
            finally
            {
                endTimer(querystr);
            }
        }
        //---------------------------------------------//
        /// <summary>
        /// Execute a query string and return nothing
        /// </summary>
        /// <param name="querystr">Query string</param>
        public void ExecuteNonReturnQuery(string querystr)
        {
            Open();

            SqlCommand cmd = new SqlCommand(querystr, con);
            cmd.CommandTimeout = 0;
            if (con.State == ConnectionState.Closed)
                con.Open();

            startTimer();
            cmd.ExecuteReader();
            endTimer(querystr);

            cmd = null;

            con.Close();
        }
        //---------------------------------------------//
        //added by jove 05/17/06
        /// <summary>
        /// Execure a query string and return nothing
        /// </summary>
        /// <param name="querystr">Query string</param>
        /// <param name="prams">Parameters</param>
        public void ExecuteNonReturnQuery(string querystr, SqlParameter[] prams)
        {
            Open();

            SqlCommand cmd = new SqlCommand(querystr, con);
            cmd.CommandTimeout = 0;
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);
                }
            }

            if (con.State == ConnectionState.Closed)
                con.Open();

            startTimer();
            cmd.ExecuteNonQuery();
            endTimer(querystr, prams);
            //cmd.ExecuteReader();
            cmd = null;

            con.Close();
        }
        #endregion
        //---------------------------------------------//
        #region trans
        /// <summary>
        /// Begin a transaction operate
        /// </summary>
        public void BeginTrans()
        {
            this.Open();

            SqlTransaction st = con.BeginTransaction();

            trans = st;
        }
        //---------------------------------------------//
        /// <summary>
        /// Rollback a transaction operate
        /// </summary>
        public void RollBackTrans()
        {
            trans.Rollback();
        }
        //---------------------------------------------//
        /// <summary>
        /// Commit a transaction operate
        /// </summary>
        public void CommitTrans()
        {
            trans.Commit();
        }
        #endregion
        //---------------------------------------------//
        #region execture store procedure
        /// <summary>
        /// execute stored procedure return a data reader, with parameter
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public SqlDataReader RunProc(string procName, SqlParameter[] prams)
        {
            Open();
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.CommandTimeout = 0;
            startTimer();
            try
            {
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            finally
            {
                endTimer(procName, prams);
            }
        }
        /// <summary>
        /// execute stored procedure return a data reader, with parameter
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public SqlDataReader RunProcForExport(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.CommandTimeout = 0;
            startTimer();
            try
            {
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            finally
            {
                endTimer(procName, prams);
            }
        }

        public SqlDataReader RunProcForTransaction(string procName, SqlParameter[] prams)
        {
            using (SqlCommand cmd = CreateCommandForTransaction(procName, prams))
            {
                startTimer();
                try
                {
                    return cmd.ExecuteReader();
                }
                finally
                {
                    endTimer(procName, prams);
                }
            }
        }

        public SqlCommand GetCommand(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.CommandTimeout = 0;
            return cmd;
        }
        //---------------------------------------------//
        /// <summary>
        /// execute stored procedure return a data reader
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public SqlDataReader RunProc(string procName)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            cmd.CommandTimeout = 0;
            startTimer();
            try
            {
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            finally
            {
                endTimer(procName, null);
            }
        }
        //---------------------------------------------//
        /// <summary>
        /// execute non query with parameters
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public void ExecNonQuery(string procName, SqlParameter[] prams)
        {
            using (SqlCommand cmd = CreateCommand(procName, prams))
            {

                cmd.CommandTimeout = 0;

                if (con.State == ConnectionState.Closed)
                    con.Open();

                startTimer();
                cmd.ExecuteNonQuery();
                endTimer(procName, prams);

                cmd.Parameters.Clear();
                this.Close();

                //cmd = null;
                cmd.Dispose();
            }
            if (_replicationFlag == "True")
            {
                try
                {
                    using (SqlCommand cmdR = CreateCommandR(procName, prams))
                    {
                        cmdR.CommandTimeout = 0;
                        if (conR.State == ConnectionState.Closed)
                            conR.Open();

                        startTimer();
                        cmdR.ExecuteNonQuery();
                        endTimer(procName, prams);

                        cmdR.Parameters.Clear();
                        this.Close();

                        cmdR.Dispose();
                    }
                }
                catch (Exception ex)
                {
                    //string e = ex.Message.ToString();
                }
            }
            //return (int)cmd.Parameters["ReturnValue"].Value;
        }

        public void ExecNonQueryForTransaction(string procName, SqlParameter[] prams)
        {
            using (SqlCommand cmd = CreateCommandForTransaction(procName, prams))
            {
                startTimer();
                cmd.ExecuteNonQuery();
                endTimer(procName, prams);
            }
            if (_replicationFlag == "True")
            {
                try
                {
                    using (SqlCommand cmdR = CreateCommandRForTransaction(procName, prams))
                    {
                        startTimer();
                        cmdR.ExecuteNonQuery();
                        endTimer(procName, prams);
                    }
                }
                catch (Exception ex)
                {
                    //string e = ex.Message.ToString();
                }
            }
            //return (int)cmd.Parameters["ReturnValue"].Value;
        }
        //---------------------------------------------//
        /// <summary>
        /// function execute non query
        /// </summary>
        /// <param name="procName"> stored procedure name</param>
        /// <returns></returns>
        public void ExecNonQuery(string procName)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            cmd.CommandTimeout = 0;
            if (con.State == ConnectionState.Closed)
                con.Open();

            startTimer();
            cmd.ExecuteNonQuery();
            endTimer(procName, null);

            this.Close();

            cmd = null;
            //return (int)cmd.Parameters["ReturnValue"].Value;
        }
        //---------------------------------------------//
        /// <summary>
        /// Create command object
        /// </summary>
        /// <param name="procName">Stored procedure's name</param>
        /// <param name="prams">Parameters</param>
        /// <returns>SqlCommand object</returns>
        public SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            Open();

            SqlCommand cmd = new SqlCommand(procName, con);
            cmd.CommandTimeout = 0;
            cmd.CommandType = CommandType.StoredProcedure;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);
                }
            }

            return cmd;
        }
        public SqlCommand CreateCommandR(string procName, SqlParameter[] prams)
        {
            OpenR();

            SqlCommand cmd = new SqlCommand(procName, conR);
            cmd.CommandTimeout = 0;
            cmd.CommandType = CommandType.StoredProcedure;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);
                }
            }

            return cmd;
        }
        public SqlCommand CreateCommandForTransaction(string procName, SqlParameter[] prams)
        {
            Open();

            SqlCommand cmd = new SqlCommand(procName, con, trans);
            cmd.CommandTimeout = 0;
            cmd.CommandType = CommandType.StoredProcedure;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);
                }
            }

            return cmd;
        }
        public SqlCommand CreateCommandRForTransaction(string procName, SqlParameter[] prams)
        {
            OpenR();

            SqlCommand cmd = new SqlCommand(procName, conR, trans);
            cmd.CommandTimeout = 0;
            cmd.CommandType = CommandType.StoredProcedure;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);
                }
            }

            return cmd;
        }
        #endregion
        //---------------------------------------------//
        #region close
        /// <summary>
        /// close database connection
        /// </summary>
        public void Close()
        {
            if (con != null)
            {
                con.Close();
            }
        }
        #endregion
        //---------------------------------------------//
        #region Judge whether the database is connection
        /// <summary>
        /// Judge whether the database is connection
        /// </summary>
        /// <returns></returns>
        public string IsOpen()
        {
            if (con == null)
                return "Close";
            else
                return "Open";
        }
        #endregion
        //---------------------------------------------//
        #region dispose
        /// <summary>
        /// release database connection
        /// </summary>
        public void Dispose()
        {
            if (con != null)
            {
                con.Dispose();

                con = null;
            }
        }
        #endregion
        //---------------------------------------------//
    }
}
